LookerのPDT作成時にSnowflakeのウェアハウスを変更してみる #looker #snowflakedb

「カスタマーストーリー」を支えるツール特集

#ビジネス

#Looker

#Snowflake

さがら

2021.02.03

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

さがらです。

Looker×Snowflakeの組合せでPDTを作成する際、作成処理の直前でSnowflakeのウェアハウスを変更する方法を検討しましたので、本ブログにてまとめてみます。

PDTって何だっけ？

ここで改めて、LookerのPDT、略さずに言うと「Persistent Derived Table」とは何かを振り返ります。

かなり端的に言うと、「Looker上で定義したSQLまたはLookMLの内容に基づいて、接続先のDWH・DBに実テーブルを作成する」機能です。

LookerでWINDOW関数を用いたり、注文単位➟店舗単位など別粒度へのデータ変換を行いたいときには派生テーブルを使わないといけませんが、この派生テーブルを実テーブル化させたものがPDTとなります。

より詳しく知りたい方は、下記の公式Docやブログが参考になるかと思います。

Looker×SnowflakeでのPDT作成時の問題点

ではここで、Looker×SnowflakeでPDTを作成しようとすると、どういった問題があるかを考えてみます。

LookerからSnowflakeに接続する際、Snowflake側のウェアハウスは、「あるウェアハウスに固定」または「ユーザー属性に応じて使用するウェアハウスをユーザーごとに変更」という方法を基本的に取ることになります。（公式Docより）

大抵の場合はウェアハウス１つで固定、または特定のユーザー属性ごとにウェアハウスを複数個用意する、といった運用になるかと思います。

しかし、とてもデータの量が多かったり処理内容が複雑で作成に時間のかかるPDTをSnowflakeに作成しようとすると、対象のPDTを作成中に、そのPDT作成に使用しているウェアハウスを用いたダッシュボードの閲覧やExploreでのグラフ作成時の動作が遅くなる可能性が高くなります。

こう述べると、「Snowflakeはウェアハウスのクラスター数を処理の負荷に応じて自動で増やすことが出来るから、問題ないのでは？」と考える方もいると思います。

なのですが、「ユーザーのデータ分析用途」と「PDTの作成用途」では、使用するウェアハウスのサイズとクラスター数の考えが異なってくると思うため、一概にクラスター数を自動で増やせることが最適とは限らないと、私は考えています。

具体的には、それぞれの用途別に、ウェアハウスについては以下の考えを持っています。

ユーザーのデータ分析用途：ウェアハウスのサイズは特別大きくなくてよいが、より多くのユーザーが使用する際に対応できるように、クラスター数の上限値を高めに設定し、自動でクラスター数を増やせるようにしたい。
PDTの作成用途：１つ１つの処理が重く複雑なものになりがちなため、ウェアハウスのサイズは大きくして、ぱぱっと処理を終わらせたい。逆に一度に複数の処理を同時に要求されることは少ないので、クラスター数の上限値は低めでよい。

このため、公式Docに沿っただけのやり方だと、「ユーザーのデータ分析用途」と「PDTの作成用途」で上手くウェアハウスを切り替える事ができず、効率が良い運用ができないリスクが発生すると思います。

参考情報

Snowflakeのウェアハウスにおける、リサイズによるスケールアップ、クラスター数増加によるスケールアウト、については下記の公式Docにも見解がまとめられていますので、ぜひご一読ください。

解決策

そこでこの問題の解決策として、「PDTを作成する際、PDT作成専用のウェアハウスに切り替える事ができれば理想的なのでは」と考えました。

実際にこの考えを実現することが出来たので、以下に手順を記します。

実装手順

0.前提条件

まず、Snowflake側にウェアハウスを2つ用意します。それぞれの用途は以下の想定です。

ユーザーのデータ分析用途：LOOKER_WH
PDTの作成用途：LOOKER_SAGARA_PDT_TEST_WH

続いて、LookerでのSnowflakeへのconnectionの設定です。

ここではポイントとなるAdditional Paramsについてのみ記載しておきます。

warehouseは、ユーザーのデータ分析用途である「LOOKER_WH」を指定
roleは、「LOOKER_WH」も「LOOKER_SAGARA_PDT_TEST_WH」も使用できるROLEを割り当てる（下図では検討のためすぐ使えるSYSADMINを割り当てていますが、これだと権限が多すぎるため、正直実際のユースケースを想定すると良い例ではありません。必要に応じて最小限の権限を与えたROLEを割り当てることをお勧めします。）

1.LookMLでのPDTの定義の記述を変更する

続いて、実際にLookerでどうやってPDT作成時にウェアハウスの切り替えを行うのか、この肝となる点を実装します。

具体的には、create_processパラメータを用いて、Snowflake内で使用可能なDDL文をPDT作成時のプロセスに組み込みます。

以下に実例を記します。create_processパラメータ内で、sql_stepサブパラメータを記述し、この中でUSE WAREHOUSEを実行した後、CREATE TABLE ~ AS SELECTを実行しているのがポイントとなります。

Before

  derived_table: {
    datagroup_trigger: sagara_snowflake_looker_test_default_datagroup
    sql: SELECT
        *
      FROM
        "SALESFORCE_SANDBOX"."FIVETRAN_FORMULA"
      WHERE
        OBJECT = 'Opportunity'
      ;;
  }

After

  derived_table: {
    datagroup_trigger: sagara_snowflake_looker_test_default_datagroup
    create_process: {
      sql_step: USE WAREHOUSE LOOKER_SAGARA_PDT_TEST_WH ;;
      sql_step: CREATE TABLE ${SQL_TABLE_NAME} AS
        SELECT
          *
        FROM
          "SALESFORCE_SANDBOX"."FIVETRAN_FORMULA"
        WHERE
          OBJECT = 'Opportunity';;
    }
  }

実装はこれだけでOKです！簡単ですね。